package Database;

import AirLine.Airline;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.TreeSet;

/**
 *
 * @author Fredrik
 */
public class Airlines extends DBObject {

    /*
     * ORACLE_033
     */
    private static final String ORACLE_033_DISCOUNT_COLUMN = "OWNDISCOUNT";
    private static final String ORACLE_033_NAME_COLUMN = "NAME";
    private static final String ORACLE_033_CODE_COLUMN = "CODE";
    private static final String ORACLE_033_AIRLINE_TABLE_NAME = "AIRLINEINFO";
    /*
     * ORACLE_013
     */
    private static final String ORACLE_013_CODE_COLUMN = "AIRLINE_ID";
    private static final String ORACLE_013_NAME_COLUMN = "NAME";
    private static final String ORACLE_013_AIRLINE_TABLE_NAME = "AIRLINE";
    private static final String ORACLE_013_OWNDISCOUNT_COLUMN = "OWN_DISSCOUNT";
    /*
     * MySQL
     */
    private static final String MYSQL_ID_COLUMN = "ID";
    private static final String MYSQL_NAME_COLUMN = "name";
    private static final String MYSQL_AIRLINE_TABLE_NAME = "Airline";
    private static final String MYSQL_DISCOUNT_COLUMN = "discount";
    private TreeSet<Airline> airlineList = null;
    private Hashtable<String, Airline> airlines = null;


    public Airlines() {
        super();
    }

    public void update() throws SQLException {
        connect();
        airlineList = new TreeSet<Airline>();
        airlines = new Hashtable<String, Airline>();
        Airline airline;

        //ORACLE 033
        PreparedStatement oracle_033_pstmnt = getConnection(ORACLE_033).prepareStatement(
                SELECT +
                ORACLE_033_CODE_COLUMN + SEPARATOR +
                ORACLE_033_NAME_COLUMN + SEPARATOR +
                ORACLE_033_DISCOUNT_COLUMN +
                FROM +
                ORACLE_033_AIRLINE_TABLE_NAME);
        ResultSet resultset_oracle033 = oracle_033_pstmnt.executeQuery();
        while (resultset_oracle033.next()) {

            String name = resultset_oracle033.getString(ORACLE_033_NAME_COLUMN);
            String code = resultset_oracle033.getString(ORACLE_033_CODE_COLUMN);
            int discount = resultset_oracle033.getInt(ORACLE_033_DISCOUNT_COLUMN);
            airline = new Airline(name, discount, code,ORACLE_033);
//            System.out.println(airportsList.contains(airport));
            addAirlineToList(airline);
        }
        // To get the Airports from "dat135_013" oracle database        
        PreparedStatement oracle_013_pstmnt = getConnection(ORACLE_013).prepareStatement(
                SELECT + 
                ORACLE_013_CODE_COLUMN + SEPARATOR + 
                ORACLE_013_NAME_COLUMN + SEPARATOR +
                ORACLE_013_OWNDISCOUNT_COLUMN +
                FROM + 
                ORACLE_013_AIRLINE_TABLE_NAME);
        ResultSet resultset_oracle013 = oracle_013_pstmnt.executeQuery();

        while (resultset_oracle013.next()) {
            String name = resultset_oracle013.getString(ORACLE_013_NAME_COLUMN);
            String code = resultset_oracle013.getString(ORACLE_013_CODE_COLUMN);
            int discount = resultset_oracle013.getInt(ORACLE_013_OWNDISCOUNT_COLUMN);
            //System.out.println(name + "--" + code);
            airline = new Airline(name, discount ,code,ORACLE_013);
            addAirlineToList(airline);
        }
        //To get the airports from mysql 013
        PreparedStatement mysql_pstmnt = getConnection(MYSQL_013).prepareStatement(
                SELECT + 
                MYSQL_ID_COLUMN + SEPARATOR + 
                MYSQL_NAME_COLUMN + SEPARATOR +
                MYSQL_DISCOUNT_COLUMN +
                FROM + 
                MYSQL_AIRLINE_TABLE_NAME);
        ResultSet resultset_mysql = mysql_pstmnt.executeQuery();
        while (resultset_mysql.next()) {
            String name = resultset_mysql.getString(MYSQL_NAME_COLUMN);
            String code = resultset_mysql.getString(MYSQL_ID_COLUMN);
            int discount = resultset_mysql.getInt(MYSQL_DISCOUNT_COLUMN);
            airline = new Airline(name, discount ,code,MYSQL_013);
            addAirlineToList(airline);

        }
        
        commit();
        resultset_mysql.close();
        resultset_oracle013.close();
        resultset_oracle033.close();
        mysql_pstmnt.close();
        oracle_013_pstmnt.close();
        oracle_033_pstmnt.close();
        closeConnections();
    }

    public int getDiscountByCode(String code) throws SQLException {
        if (airlines == null ) {
            populate();
        }
        
        return (airlines.get(code)).getDiscount();
    }
    /*
    public ArrayList<Airline> getAirlineByDatabase(String dbId ) throws SQLException{
        ArrayList<Airline> retval = new ArrayList<Airline>();
        if(airlineList == null){
            update();
        }
        Iterator<Airline> airlineIterator = airlineList.iterator();
        while(airlineIterator.hasNext()){
            Airline toAdd = airlineIterator.next();
            if(toAdd.getDbIdentifier().equals(dbId)){
                retval.add(toAdd);
            }
        }
        return retval;
    }
    */
    public Airline getAirlineByDatabase(String dbId ) throws SQLException
    {
        if(airlineList == null){
            update();
        }
        for(Airline a: airlineList)
        {
            if (dbId.equalsIgnoreCase(a.getDbIdentifier()))
                return a;
        }
        return null;
    }
    
    public void populate() throws SQLException {
        if (airlines == null || airlineList == null) {
            update();
        }
    }
    
        public Airline getAirlineByCode(String code) throws SQLException {
        if (airlines == null) {
            populate();
        }

        return airlines.get(code);
    }
    
    public TreeSet<Airline> getAirlines() throws SQLException{
        if(airlineList == null){
            update();
        }
        return airlineList;
    }

    private void addAirlineToList(Airline a) {
        if (!airlines.containsKey(a.getCode())) {
            airlines.put(a.getCode(), a);
            airlineList.add(a);
        }

    }
}
